<?php
include(dirname(__FILE__)."/../Dao/db.php");
include_once(dirname(__FILE__)."/../Service/CommonClass/MailSendClass.php");
class SendAlertMailBatch
{
	var $accountList = array();
	var $startDate ;
	var $objectDate ;
	
	function SendAlertMailBatch()
	{
		$this->startDate = date("Y-m-01",mktime( date("H"), date("i")-60, date("s"), date("m"), date("d"), date("Y")));
		$this->objectDate = date("Y-m-d",mktime( date("H"), date("i")-60, date("s"), date("m"), date("d"), date("Y")));
	}
	
	function run()
	{
		$this->sendAdwordsAlert();
	}
	
	
	function sendAdwordsAlert()
	{
		$this->getAccountList();
		foreach($this->accountList as $account){
			$memberId = $account[0];
			$loginId = $account[1];
			$mailAddress = $account[4];
			$company = $account[2];
			$accountContents = "";
			
			// [Listing全体]
			// AdWords月間 : 予算との乖離率 = -85 % [全期間の予算額:5,000 当日までのご利用金額:501]
			if($account[5] != 0 || $account[6] != 0)
			{
				$accountContents = $this->getAccountCost($memberId,$account[5],$account[6]);
			}
			//echo "[Listing全体]".$accountContents;
			
			// [キャンペーン単位]
			// AdWords - vagetable : 予算との乖離率 = -100 % [全期間の予算額:100 当日までのご利用金額:0]]
			$campContents = $this->getCampCost($memberId);
			
			//echo "[キャンペーン単位".$campContents;
			
			
			$this->sendMail($company,$loginId,$mailAddress,$accountContents,$campContents);
		}
	}
	
	function getAccountCost($memberId,$budget,$budgetLimit)
	{
		$mailContents = "";
		$sql = " select member.memberId,sum(cost) cost,".date('t',$this->objectDate)."/DAYOFMONTH(now()) dayMonth ";
		$sql.= " from adreport join member on adreport.memberId = member.memberId ";
		$sql.= " where member.status = 1 ";
		$sql.= " and TO_DAYS(date) >= TO_DAYS('".$this->startDate."') ";
		$sql.= " and TO_DAYS(date) <= TO_DAYS('".$this->objectDate."') ";
		$sql.= " group by member.memberId ";
		$sql.= " having member.memberId =".$memberId;
		
		$result = mysql_query($sql);
		//echo $sql;
		$rows = mysql_num_rows($result);
		if ($rows > 0)
		{
			$data = mysql_fetch_row($result);
			$nowCost = $data[1];
			$aimCost = $budget*$data[2];
			$maxCost = $aimCost*(1+$budgetLimit/100);
			$minCost = $aimCost*(1-$budgetLimit/100);
			//echo $budget." ".$budgetLimit/100;
			//$mailContents = "[Listing全体]".$budget." ".$budgetLimit."nowCost:".$nowCost." aimCost:".$aimCost." maxCost:".$maxCost." minCost:".$minCost;
			$mailContents .=  "AdWords月間 : 予算との乖離率 = ".(int)(($nowCost-$aimCost)/$aimCost*100)." %";
			$mailContents .=  "[全期間の予算額:".$budget." 当日までのご利用金額:".$nowCost."]\r\n";
			return $mailContents;
		}
		else
		{
			return "";
		}
	} 
	
	function getCampCost($memberId)
	{
		$mailContents = "";
		$campCost = array();
		$sql = " SELECT campaign.camp_Id,sum(cost) cost ";
		$sql.= " from campaign join alertschedule on campaign.camp_Id = alertschedule.campaignId ";
		$sql.= "   join adreport on adreport.campaign_Id = campaign.camp_Id ";
		$sql.= " where campaign.memberId =".$memberId;
		$sql.= " and ( "; 
		$sql.= " 	(period = 1 and TO_DAYS(date) >= TO_DAYS('".$this->startDate."') )";
		$sql.= "  OR (period = 2 and TO_DAYS(date) >= TO_DAYS(startDay) )";
		$sql.= "  OR (period = 3 and TO_DAYS(date) >= TO_DAYS('".$this->objectDate."') )";
		$sql.= " ) "; 
		$sql.= " and TO_DAYS(date) <= TO_DAYS('".$this->objectDate."') ";
		$sql.= " group by campaign.camp_Id ";
		$result = mysql_query($sql);
		$rows = mysql_num_rows($result);
		//echo $sql;
		if ($rows > 0)
		{
			for ($i=0;$i<$rows;$i++)
			{
				$data = mysql_fetch_row($result);
				$campCost[$data[0]] = $data[1] ;
			}
		}
		
		//print_r($campCost);
				
		$sql = " SELECT campaign.name,campaign.camp_Id,(TO_DAYS('".$this->objectDate."')-TO_DAYS(startDay))/(TO_DAYS(endDay)-TO_DAYS(startDay)) dayCamp, ";
		$sql.= date('t',$this->objectDate)."/DAYOFMONTH(now()) dayMonth , period,alertBudget,conversionPremium ";
		$sql.= " FROM campaign join alertschedule on campaign.camp_Id = alertschedule.campaignId ";
		$sql.= " WHERE alertBudget is not null and alertBudget<>0 ";
		$sql.= "   and conversionPremium is not null and conversionPremium<>0 ";
		$sql.= "   and (period <> 2 or campaign.endDay is not null) and campaign.memberId =".$memberId;
//echo $sql;
		$result = mysql_query($sql);
		$rows = mysql_num_rows($result);
		if ($rows > 0)
		{	
			for ($i=0;$i<$rows;$i++)
			{
				$data = mysql_fetch_row($result);
				$campName = $data[0];
				$period = $data[4];
				$budget = $data[5];
				$budgetLimit = $data[6];
				if( isset($campCost[$data[1]]) )
				{
					$nowCost = $campCost[$data[1]];
				}
				else
				{
					$nowCost = 0;
				}
				//echo $data[1] ." @" .$campCost[$data[1]] . "<br>";
				//期間 1:月単位、2:キャンペーン期間、3:日単位
				if($period = 1)
				{
					$aimCost = $budget*$data[3];
				}
				else if ($period = 2)
				{
					$aimCost = $budget*$data[2];
				}
				else
				{
					$aimCost = $budget;
				}
				$maxCost = $aimCost*(1+$budgetLimit/100);
				$minCost = $aimCost*(1-$budgetLimit/100);
				//$mailContents.= "\r\n".$campName." ".$data[1]." ".$period." ".$budget." ".$budgetLimit/100;
				//$mailContents.= "\r\nnowCost:".$nowCost." aimCost:".$aimCost." maxCost:".$maxCost." minCost:".$minCost;
				if( $nowCost > $maxCost || $nowCost < $minCost )
				{
					$mailContents .=  "AdWords - ".$campName." : 予算との乖離率 = ".(int)(($nowCost-$aimCost)/$aimCost*100)." %";
					$mailContents .=  "[全期間の予算額:".$budget." 当日までのご利用金額:".$nowCost."]\r\n";
				}
			}
			return $mailContents;
		}else{
			return "";
		}
		
	}
	
	function sendMail($company,$loginId,$mailAddress,$accountContents,$campContents)
	{
		if ($accountContents != "" || $campContents != "" )
		{
			echo "【LoginID】".$loginId." アラートデータはあります。\n";
			$mailClass = new Mail;
			$mailContents = $company."　ご担当者　様\r\n\r\n";
			$mailContents.= $this->objectDate."分の監視結果です。\r\n\r\n";
			$mailContents.= "お客様のリスティングキャンペーンにて、予算と実績との乖離\r\n（予算超過、または予算未達）が見受けらました。\r\n";
				
			if ($accountContents != "")
			{
				$mailContents.= "\r\n[Listing全体]\r\n";
				$mailContents.= $accountContents;			
			}
			
			if ($campContents != "")
			{
				$mailContents.= "\r\n[キャンペーン単位]\r\n";
				$mailContents.= $campContents;			
			}	
			$mailAddress = ereg_replace("\r\n",";",$mailAddress);
			$mailList = split(";",$mailAddress);
			echo $mailContents."\n";
			//print_r($mailList);
			foreach($mailList as $mailBox)
			{	
				$send = $mailClass->sendmail($mailBox, "adlisting@s268.xrea.com", "【ADLISTING】 予実監視レポート (".$company.")", $mailContents);
				if($send) 
				{
					echo "【LoginID】".$loginId."のメール(".$mailAddress.")成功に送信しました。\n";
				}
				else
				{
					echo "【LoginID】".$loginId."のメール(".$mailAddress.")送信が失敗しました。\n";
				}
			}
		}
		else
		{
			echo "【LoginID】".$loginId." アラートデータはありません。\n";
		}
		//echo "\r\nsendMail()";
	}
	
	function getAccountList()
	{
		$sql = " SELECT member.memberId,member.loginid,member.company,account.id,budgetEmail, ";
		$sql.= " if(budget is null,0,budget) as budget,if(budgetLimit is null,0,budgetLimit) as budgetLimit ";
		$sql.= " FROM member join account on member.memberId = account.memberId ";
		$sql.= " WHERE member.status = 1 and budgetEmail is not null order by memberId ";
		
		$result = mysql_query($sql);
		//echo $sql;
		while ($row = mysql_fetch_array($result)) {
			array_push($this->accountList,$row);
		}
	} 
		
}


$instance = new SendAlertMailBatch;
$instance->run();

?>
